﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using DistributeTransaction.Models;

namespace DistributeTransaction.DAL
{
    /// <summary>
    /// if can not  use Linq to SQL, write down your raw sql, 
    /// otherwise, we can use Linq to sql
    /// </summary>
    public class CardRepository : GenericRepository<CardInfo>
    {
        public CardRepository(DatabaseContext context)
            : base(context)
        {

        }

        internal IEnumerable<CardInfo> GetCardInfos(Guid userId)
        {
            const string query = @"SELECT 
[AccountId]
,[CardNumber]
,[HoldName]
,[CardType]
,[ExpireMonth]
,[ExpireYear]
,[ValidCode]
FROM [dbo].[CardInfo] WITH(NOLOCK)
WHERE UserId = @p0";
            return GetWithRawSql(query, userId).ToList();
        }

        internal CardInfo FindCardById(int id)
        {
            return this.dbContext.CardInfos.FirstOrDefault(e => e.AccountId == id);
            
            /*
            const string query = @"SELECT 
[AccountId]
,[CardNumber]
,[HoldName]
,[CardType]
,[ExpireMonth]
,[ExpireYear]
,[ValidCode]
FROM [dbo].[CardInfo] WITH(NOLOCK)
WHERE AccountId = @p0";
            return GetWithRawSql(query, id).FirstOrDefault();
            */
        }


        internal void DeleteCardById(int Id)
        {
            const string sql = @"dbo.Delete_CardInfo";

            SqlParameter pAccountId = new SqlParameter();
            pAccountId.ParameterName = "AccountId";
            pAccountId.Direction = ParameterDirection.Input;
            pAccountId.DbType = DbType.Int32;
            pAccountId.Value = Id;

            ExecuteStoreProcedure(sql, pAccountId);
        }

        internal bool CreatNewItem(CardInfo cardInfo, Guid customerId)
        {
            const string sql = "dbo.Insert_CardInfo";

            SqlParameter pUserId = new SqlParameter();
            pUserId.ParameterName = "UserId";
            pUserId.Direction = ParameterDirection.Input;
            pUserId.DbType = DbType.Guid;
            pUserId.Value = customerId;

            SqlParameter pCardNumber = new SqlParameter();
            pCardNumber.ParameterName = "CardNumber";
            pCardNumber.Direction = ParameterDirection.Input;
            pCardNumber.DbType = DbType.String;
            pCardNumber.Value = cardInfo.CardNumber;

            SqlParameter pHoldName = new SqlParameter();
            pHoldName.ParameterName = "HoldName";
            pHoldName.Direction = ParameterDirection.Input;
            pHoldName.DbType = DbType.String;
            pHoldName.Value = cardInfo.HoldName;

            SqlParameter pCardType = new SqlParameter();
            pCardType.ParameterName = "CardType";
            pCardType.Direction = ParameterDirection.Input;
            pCardType.DbType = DbType.String;
            pCardType.Value = cardInfo.CardType;

            SqlParameter pExpireMonth = new SqlParameter();
            pExpireMonth.ParameterName = "ExpireMonth";
            pExpireMonth.Direction = ParameterDirection.Input;
            pExpireMonth.DbType = DbType.String;
            pExpireMonth.Value = cardInfo.ExpireMonth;

            SqlParameter pExpireYear = new SqlParameter();
            pExpireYear.ParameterName = "ExpireYear";
            pExpireYear.Direction = ParameterDirection.Input;
            pExpireYear.DbType = DbType.String;
            pExpireYear.Value = cardInfo.ExpireYear;

            SqlParameter pValidCode = new SqlParameter();
            pValidCode.ParameterName = "ValidCode";
            pValidCode.Direction = ParameterDirection.Input;
            pValidCode.DbType = DbType.String;
            pValidCode.Value = cardInfo.ValidCode;

            SqlParameter pAccountId = new SqlParameter();
            pAccountId.ParameterName = "AccountId";
            pAccountId.Direction = ParameterDirection.Output;
            pAccountId.DbType = DbType.Int32;

            bool ret = ExecuteStoreProcedure(sql, pUserId, pCardNumber, pHoldName, pCardType, pExpireMonth, pExpireYear, pValidCode, pAccountId);
            if (ret)
            {
                cardInfo.AccountId = (int)pAccountId.Value;
            }
            return ret;
        }

        internal bool SaveCardInfo(CardInfo cardInfo)
        {
            const string sql = "dbo.Update_CardInfo";

            SqlParameter pAccountId = new SqlParameter();
            pAccountId.ParameterName = "AccountId";
            pAccountId.Direction = ParameterDirection.Input;
            pAccountId.DbType = DbType.Int32;
            pAccountId.Value = cardInfo.AccountId;

            SqlParameter pCardNumber = new SqlParameter();
            pCardNumber.ParameterName = "CardNumber";
            pCardNumber.Direction = ParameterDirection.Input;
            pCardNumber.DbType = DbType.String;
            pCardNumber.Value = cardInfo.CardNumber;

            SqlParameter pHoldName = new SqlParameter();
            pHoldName.ParameterName = "HoldName";
            pHoldName.Direction = ParameterDirection.Input;
            pHoldName.DbType = DbType.String;
            pHoldName.Value = cardInfo.HoldName;

            SqlParameter pCardType = new SqlParameter();
            pCardType.ParameterName = "CardType";
            pCardType.Direction = ParameterDirection.Input;
            pCardType.DbType = DbType.String;
            pCardType.Value = cardInfo.CardType;

            SqlParameter pExpireMonth = new SqlParameter();
            pExpireMonth.ParameterName = "ExpireMonth";
            pExpireMonth.Direction = ParameterDirection.Input;
            pExpireMonth.DbType = DbType.String;
            pExpireMonth.Value = cardInfo.ExpireMonth;

            SqlParameter pExpireYear = new SqlParameter();
            pExpireYear.ParameterName = "ExpireYear";
            pExpireYear.Direction = ParameterDirection.Input;
            pExpireYear.DbType = DbType.String;
            pExpireYear.Value = cardInfo.ExpireYear;

            SqlParameter pValidCode = new SqlParameter();
            pValidCode.ParameterName = "ValidCode";
            pValidCode.Direction = ParameterDirection.Input;
            pValidCode.DbType = DbType.String;
            pValidCode.Value = cardInfo.ValidCode;


            return ExecuteStoreProcedure(sql, pAccountId, pCardNumber, pHoldName,pCardType, pExpireMonth, pExpireYear, pValidCode);
        }
    }
}